package cn.hpclub.server.model;

import java.io.Serializable;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import com.jfinal.ext.plugin.tablebind.TableBind;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Model;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;

import cn.hpclub.server.constant.Constant;

/**
 * 实体类 - 宽带预约订单
 * 
 */
@TableBind(tableName = "service_reservation")
public class ServiceReservation extends Model<ServiceReservation> implements Serializable{
    private static final long              serialVersionUID = 5108597504836776210L;
    public static final ServiceReservation dao              = new ServiceReservation();
    public static String                   BOSS_NO          = "boss_no";
    public static String                   TELEPHONE        = "telephone";
    public static String                   CUSTOMER_NAME    = "customer_name";
    public static String                   USER_ID          = "user_id";
    public static String                   CREATE_DATE      = "create_date";
    public static String                   SERVICE_TYPE     = "service_type";
    public static String                   REWARD           = "reward";

    public boolean save(ServiceReservation model){
        return (model.save());
    }

    public boolean delete(String[] ids){
        StringBuffer sb = new StringBuffer();
        for(String id : ids){
            sb.append(id).append(",");
        }
        if(sb.length() > 0){
            sb.deleteCharAt(sb.length() - 1);
        }
        String sql = "UPDATE service_reservation SET is_deleted=1 WHERE id in (" + sb.toString() + " )";
        return Db.update(sql) > 0 ? true : false;
    }

    public List<Record> queryAllOrderData(int dyid, String starttime, String endtime){
        String sql = "select date_format(create_date, '%Y-%m-%d') as SaleDate,count(create_date) as SaleAmount from service_reservation where appid=100000056 and user_id="
                + "?" + " and date_format(create_date, '%Y-%m-%d') >= date_format('" + starttime
                + "', '%Y-%m-%d') and date_format(create_date, '%Y-%m-%d') <= date_format('" + endtime
                + "', '%Y-%m-%d') and service_type=2 and is_deleted=0 group by SaleDate desc;";

        return Db.find(sql, dyid);
    }

    public List<Record> queryAllTerminalOrderData(int dyid, String starttime, String endtime){
        String sql = "select date_format(create_time, '%Y-%m-%d') as SaleDate,count(create_time) as SaleAmount "
                + "from order_info where sales_id =" + "?"
                + " and date_format(create_time, '%Y-%m-%d') >= date_format('" + starttime
                + "', '%Y-%m-%d') and date_format(create_time, '%Y-%m-%d') <= date_format('" + endtime
                + "', '%Y-%m-%d')  group by SaleDate desc;";

        return Db.find(sql, dyid);
    }

    /**
     * @Title: getBroadbandOrderList
     * @Description:根据userid获取宽带预约订单列表
     * @param pageNumber
     * @param pageSize
     * @param userId
     * @param keyword
     * @return Page<Record>
     */
    public Page<Record> getBroadbandOrderList(int pageNumber, int pageSize, int userId, String keyword){
        // String select = "select bdorder.boss_no as
        // boss_no,bdorder.customer_name as customer_name,bdorder.telephone as
        // customer_telephone,date_format(bdorder.create_date,
        // '%Y/%m/%d%H:%i:%s') as create_date,(case bdorder.service_type when 2
        // then '宽带预约' end) as service_type,(case bdorder.is_pay when 0 then
        // '未支付' when 1 then '已支付' end) as pay_status,member.name as
        // clerk_name,member.mobile as clerk_mobile,bd.name as bd_name";
        // String sqlExceptSelect = " from service_reservation as bdorder left
        // join member_org as member on bdorder.user_id=member.id left join
        // broadband as bd on bdorder.broadband_id=bd.id"
        // + " where bd.is_deleted=0 && bdorder.appid=100000056 and
        // bdorder.service_type=2 and bdorder.is_deleted=0 and bdorder.boss_no
        // != '' and bdorder.user_id="
        // + "?";
        String select = "select bdorder.boss_no as boss_no,bdorder.customer_name as customer_name,bdorder.telephone as customer_telephone,date_format(bdorder.create_date, '%Y/%m/%d%H:%i:%s') as create_date,(case bdorder.service_type when 2 then '宽带预约' end) as service_type,(case bdorder.is_pay when 0 then '未支付' when 1 then '已支付' end) as pay_status,member.name as clerk_name,member.mobile as clerk_mobile,bd_name";
        String sqlExceptSelect = " from service_reservation as bdorder left join member_org as member on bdorder.user_id=member.id"
                + " where bdorder.appid=100000056 and bdorder.service_type=2 and bdorder.is_deleted=0 and bdorder.boss_no != '' and bdorder.user_id="
                + "?";

        if(StrKit.notBlank(keyword)){
            sqlExceptSelect += " and (bdorder.boss_no='" + keyword + "' or bdorder.telephone='" + keyword
                    + "' or bdorder.customer_name like '%" + keyword + "%')";
        }

        // System.out.println("sql = " + select + sqlExceptSelect);
        return Db.paginate(pageNumber, pageSize, select, sqlExceptSelect, userId);
    }

    /**
     * @Title: queryReward
     * @Description:TODO
     * @param userid
     * @param time
     * @return List<ServiceReservation>
     */
    public List<ServiceReservation> queryReward(int userid, String time){
        return find("select count(*) as SaleCount,sum(" + REWARD + ") as Amount," + CREATE_DATE + " as time from "
                + Constant.Table.SERVICE_RESERVATION + " where appid=" + Constant.APPID + " and " + SERVICE_TYPE
                + "=2 and " + USER_ID + "=? and date_format(" + CREATE_DATE + ",'%Y-%m')='" + time + "'",
                    new Object[] { userid });
    }

    /**
     * @Title: getServiceReservationCount
     * @Description:统计移动宽带订单数量,未加过滤条件
     * @return Long
     */
    public Long getServiceReservationCount(){
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String today = sdf.format(new Date());
        // int org_id = InterfaceUtil.getOrgId();

        String sql = " select count(1) as orderCnt from service_reservation sr "
                + " where date_format(sr.create_date,'%Y-%m-%d') = ?  ";
        // + " and sr.org_id in("
        // + InterfaceUtil.getChlidOrgIdSql(org_id, Constant.MAX_ORG_LEVEL,
        // true) + ") ";
        Record orderCnt = Db.findFirst(sql, today);
        Long count = orderCnt.get("orderCnt");

        return count;
    }

    /**
     * 
     * @Title: updateGovBdOrder
     * @Description:TODO
     * @param boss_no:dpr接口返回的流水号
     * @param order_id
     * @return boolean
     */
    public boolean updateGovBdOrder(long boss_no, long order_id){
        String sql = "update service_reservation set boss_no=? where order_id=?";
        int count = Db.update(sql, boss_no, order_id);
        if(count >= 1){
            return true;
        }

        return false;
    }
}
